﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.IO;
using Excel;
using DevExpress.XtraReports;
using DevExpress.XtraReports.UI;
namespace Exact_Extension
{
    public partial class SendProformaInvForm : DevExpress.XtraEditors.XtraForm
    {
        ExactDataContext DS;
        IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
        Common common;
        public SendProformaInvForm(ExactDataContext _DS)
        {
            InitializeComponent();
            DS = _DS;
            this.fromdate.EditValue = DateTime.Now.Date;
            this.todate.EditValue = DateTime.Now.Date;
            this.txtSubject.EditValue = "ISHCMC Proforma Invoice TERM 2 of school year 2013-2014 for Student <<StudentID>>  <<StudentName>> ";
            this.txtBCC.EditValue = "Accounting@ishcmc.edu.vn";
            common = new Common(DS);
            this.LoadDataInitalize();
            
        }
        private void LoadDataInitalize()
        {
            var querySec = from Sec in DS.ISHCMC_Sections
                           select Sec;
            this.iSHCMCSectionBindingSource.DataSource = querySec;

            var querySCHY = from SCHY in DS.ISHCMC_SchoolYears
                            select SCHY;
            this.iSHCMCSchoolYearBindingSource.DataSource = querySCHY;
        }
    
        private void btnLoad_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {

           
        }

        private void btnSend_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            this.SendEmailContent();
        }
        private void SendEmailContent()
        {
            foreach (int i in this.gridView_Student.GetSelectedRows())
            {
                
                ISHCMC_V_StudentListWithDebtor itemStudent = (ISHCMC_V_StudentListWithDebtor)this.gridView_Student.GetRow(i);
                //itemStudent.Email1 = "dbaadmin@ishcmc.edu.vn";
                //itemStudent.Email2 = "nguyenphuoctan@gmail.com";
                //itemStudent.EmailBilling = "tan.nguyen@cognitaschools.sg";


                FileInfo fileTemplate = new FileInfo("EmailTemplate\\ProfromInvLetter.docx");
                this.richEditControl1.LoadDocument(fileTemplate.OpenRead(), DevExpress.XtraRichEdit.DocumentFormat.OpenXml);
                       
                        


                   
            
              
                string html = this.richEditControl1.HtmlText;

                html = html.Replace("IT-PARENT", itemStudent.InvoiceDebtorName);
                    
               
                this.richEditControl1.HtmlText = html;
                this.sendEMailThroughHotMail(html, itemStudent);
                int CountSecond = 0;
                while (CountSecond < 10000)
                {
                    CountSecond++;
                }
            }
           
        }
        private void gridView_Student_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)
        {

            

            
        }
         private void PrintProformaInv(ISHCMC_V_StudentListWithDebtor student,string filename)
        {
            if (lookUpEdit_SchoolYear.EditValue != null)
            {
                if (lookUpEdit_SchoolYear.EditValue.ToString().Length == 4)
                {
                    RootRpt rootrpt = new RootRpt();
                    rootrpt.CreateDocument();

              

                        if (student.Invoicing == true)
                        {
                            List<ISHCMC_ListInvoiceDetail> ListInvDetail = new List<ISHCMC_ListInvoiceDetail>();
                            var queryInv = from InvoiceList in DS.ISHCMC_Invoices
                                           join ListItem in DS.Items
                                               on InvoiceList.Itemcode equals ListItem.ItemCode
                                           where InvoiceList.StudentID == student.StudentID
                                           && InvoiceList.SchoolYear == lookUpEdit_SchoolYear.EditValue.ToString()
                                           && InvoiceList.Status == 0 && InvoiceList.Section == this.lookupEdit_Section.EditValue.ToString()
                                           && ListItem.Assortment !=0
                                           select InvoiceList;



                            foreach (ISHCMC_Invoice itemInv in queryInv)
                            {
                                if (itemInv.Status == 0)
                                {

                                    if (itemInv.SpecialDiscount.Value == true)
                                    {
                                        if (itemInv.Authorize.Value == true)
                                        {
                                            ListInvDetail.Add(common.GetInvoiceDetail(itemInv));
                                        }
                                    }
                                    else
                                    {
                                        if (itemInv.Itemcode == "COE")
                                        {
                                            if (common.CheckStudentAssignedCOE(itemInv.StudentID))
                                            {
                                                ListInvDetail.Add(common.GetInvoiceDetail(itemInv));
                                            }

                                        }
                                        else
                                        {
                                            ListInvDetail.Add(common.GetInvoiceDetail(itemInv));
                                        }
                                    }
                                }
                            }
                            if (ListInvDetail.Count() > 0)
                            {
                                if (student.School == "ISHCMC")
                                {
                                    ProformaInvRptSigned rpt = new ProformaInvRptSigned();
                                    ISHCMC_StudentPayment itemPaid = common.GetStudentPayment(student.StudentID);
                                    rpt.SetListInvoiceDetail(ListInvDetail, itemPaid, student);
                                    rpt.SetTitle("SCHOOL FEES FOR " + DateTime.Now.Year.ToString() + " / " + (DateTime.Now.Year + 1).ToString() + " ACADEMIC YEAR");
                                    rpt.SetAttention(common.GetStudentAttention(student.StudentID));
                                    rpt.CreateDocument();
                                    rootrpt.Pages.AddRange(rpt.Pages);
                                    rootrpt.PrintingSystem.ContinuousPageNumbering = true;
                                }
                                if (student.School == "AAVN")
                                {
                                    ProformaInvAARptSigned rpt = new ProformaInvAARptSigned();
                                    ISHCMC_StudentPayment itemPaid = common.GetStudentPayment(student.StudentID);
                                    rpt.SetListInvoiceDetail(ListInvDetail, itemPaid);
                                    rpt.SetTitle("SCHOOL FEES FOR " + DateTime.Now.Year.ToString() + " / " + (DateTime.Now.Year + 1).ToString() + " ACADEMIC YEAR");
                                    rpt.SetAttention(common.GetStudentAttention(student.StudentID));
                                    rpt.CreateDocument();
                                    rootrpt.Pages.AddRange(rpt.Pages);
                                    rootrpt.PrintingSystem.ContinuousPageNumbering = true;
                                }
                            }

                        }



                        rootrpt.ExportToPdf(filename);
                }
                else
                {
                    MessageBox.Show("Please choose School Year !!");
                }
            }
            else
            {
                MessageBox.Show("Please choose School Year !!");
            }
        }

        //method to send email to HOTMAIL
        public void sendEMailThroughHotMail(string contentHTML,ISHCMC_V_StudentListWithDebtor itemStudent)
        {
            try
            {
                //string emailAccount = System.Configuration.ConfigurationSettings.AppSettings["AccountEmail"];
                //string password = System.Configuration.ConfigurationSettings.AppSettings["AccountEmailPass"];
                //string smtpserver = System.Configuration.ConfigurationSettings.AppSettings["SMTPServer"];
                //string smtpport = System.Configuration.ConfigurationSettings.AppSettings["SMTPPort"];

                string emailAccount ="dbaadmin@ishcmc.edu.vn";
                string password = "#P@ssword#";
                string smtpserver = "pod51012.outlook.com";
                string smtpport = "587";

                //Mail Message
                MailMessage mM = new MailMessage();
                //Mail Address
                mM.From = new MailAddress(emailAccount);
                //receiver email id
                if (itemStudent.Email1.Length > 5)
                {
                    mM.To.Add(itemStudent.Email1);
                }
                if (itemStudent.Email2.Length > 5)
                {
                    mM.To.Add(itemStudent.Email2);
                }
                if (itemStudent.EmailBilling.Length > 5)
                {
                    mM.To.Add(itemStudent.EmailBilling);
                }
              
                if (this.txtBCC.EditValue != null)
                {
                    mM.Bcc.Add(this.txtBCC.EditValue.ToString());
                }
                mM.Bcc.Add(emailAccount);
                if (this.txtCC.EditValue != null)
                {
                    mM.CC.Add(this.txtCC.EditValue.ToString());
                }
                //subject of the email
                mM.Subject = this.txtSubject.EditValue.ToString();
                mM.Subject = mM.Subject.Replace("<<StudentID>>" , itemStudent.StudentID);
                mM.Subject = mM.Subject.Replace("<<StudentName>>" , itemStudent.StudentName);
               
                //deciding for the attachment
               
    
                AlternateView avHtml = AlternateView.CreateAlternateViewFromString(contentHTML, null, System.Net.Mime.MediaTypeNames.Text.Html);
              

                string filename = "ProfromaInvoice2013-2014_"+this.lookupEdit_Section.EditValue.ToString() +"_"+ itemStudent.StudentName+"_"+itemStudent.StudentID + ".pdf";
                this.PrintProformaInv(itemStudent,filename);

                mM.Attachments.Add(new Attachment(filename));



                mM.AlternateViews.Add(avHtml);

               
               
              
                //SMTP client
                SmtpClient sC = new SmtpClient(smtpserver);
                //port number for Hot mail
                sC.Port = Convert.ToInt32(smtpport);
                //credentials to login in to hotmail account

                sC.Credentials = new NetworkCredential(emailAccount,password);
                //enabled SSL
                sC.EnableSsl = true;

                //Send an email
                if (mM.To.Count > 0)
                {
                    sC.Send(mM);


                    string _SentTo = "";
                    foreach (MailAddress itemSentTo in mM.To)
                    {
                        _SentTo += itemSentTo.Address + ";";
                    }
                    string _CC = "";
                    foreach (MailAddress itemCC in mM.CC)
                    {
                        _CC += itemCC.Address + ";";
                    }
                    string _BCC = "";
                    foreach (MailAddress itemBCC in mM.Bcc)
                    {
                        _BCC += itemBCC.Address + ";";
                    }

                  
               
                    DS.SubmitChanges();
                }
            }//end of try block
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }//end of catch
        }

        private void timer1_Tick(object sender, EventArgs e)
        {

        }

        private void btnBrowse_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            this.openFileDialog1.ShowDialog();
        }

        private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            this.txtFileName.EditValue = this.openFileDialog1.FileName;
            List<ISHCMC_V_StudentListWithDebtor> ListStudent = new List<ISHCMC_V_StudentListWithDebtor>();
            FileInfo fileInfo = new FileInfo(this.openFileDialog1.FileName);
            FileStream stream = File.Open(this.openFileDialog1.FileName, FileMode.Open, FileAccess.Read);
             IExcelDataReader excelReader = null ;
             if (fileInfo.Extension == ".xls")
             {
                 //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                 excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
             }
             if (fileInfo.Extension == ".xlsx")
             {
                 //...
                 //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                 excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
             }
            //...
       
            //4. DataSet - Create column names from first row
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();

            //5. Data Reader methods
            while (excelReader.Read())
            {
                string studentID = excelReader.GetString(1);
                var queryStudent = from ST in DS.ISHCMC_V_StudentListWithDebtors
                                   where ST.StudentID == studentID
                                   select ST;
                ListStudent.AddRange(queryStudent.ToArray());

            }

            //6. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();
            this.iSHCMCVStudentListWithDebtorExtBindingSource.DataSource = ListStudent;
        }

       
    }
}